from flask import flash, Blueprint, render_template, request, jsonify, redirect, url_for, session
from app.config import create_db_connection
from app.config import compress_and_save_image
from datetime import datetime
import os
import imghdr
import tempfile

warehousing_entry_add_bp = Blueprint('warehousing_entry_add', __name__)

@warehousing_entry_add_bp.route('/warehousing_entry_add', methods=['GET', 'POST'])
def warehousing_entry_add():
    conn, cursor = create_db_connection()

    cursor.execute("SELECT * FROM customer_list")
    customer_list = cursor.fetchall()

    current_datetime = "QTRK" + datetime.now().strftime("%Y%m%d%H%M%S")

    cursor.execute("SELECT * FROM product_list")
    product_list = cursor.fetchall()

    product_info = [('N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 'N/A')]

    # cursor.execute("SELECT * FROM product_list WHERE id = 2")
    # product_info = cursor.fetchall()
    cursor.close()
    conn.close()
    return render_template('warehouse/warehousing_entry_add.html', customer_list=customer_list,
                           current_datetime=current_datetime, product_list=product_list, product_info=product_info)


@warehousing_entry_add_bp.route('/get_product_list', methods=['GET'])
def get_product_list():
    selected_value = request.args.get('selected_value')

    if not selected_value:
        updated_options = [{'value': "", 'text': "请先选择客户！"}]
        return jsonify(updated_options)

    conn, cursor = create_db_connection()
    cursor.execute("SELECT * FROM product_list WHERE customer_id = %s", (selected_value,))
    product_list = cursor.fetchall()
    # print(product_list)

    updated_options = []
    updated_options.append({'value': "", 'text': "请选择产品！"})

    for item in product_list:
        value = item[0]
        text = '-'.join(str(item[i]) for i in range(3, 6) if isinstance(item[i], str))

        updated_options.append({'value': str(value), 'text': text})

    # print(updated_options)
    cursor.close()
    conn.close()
    return jsonify(updated_options)


@warehousing_entry_add_bp.route('/get_product_info', methods=['GET'])
def get_product_info():
    product_id = request.args.get('selected_value')

    # 根据产品ID查询相应的产品信息，这里假设product_info是一个包含产品信息的字典
    product_info = query_product_info(product_id)

    return jsonify(product_info)


# 实际查询产品信息的函数
def query_product_info(product_id):
    # 查询产品信息的逻辑，返回产品信息的字典
    conn, cursor = create_db_connection()
    cursor.execute("SELECT * FROM product_list WHERE id = %s", (product_id,))
    product_info = cursor.fetchall()

    # 获取列标题
    column_names = [desc[0] for desc in cursor.description]

    # 构建结果字典
    result = []
    for row in product_info:
        row_data = dict(zip(column_names, row))
        result.append(row_data)
    cursor.close()
    conn.close()
    return result


@warehousing_entry_add_bp.route('/warehousing_entry_submit', methods=['GET', 'POST'])
def warehousing_entry_submit():
    # 获取表单数据
    customer_id = request.form.get('customer')
    enter_time = request.form.get('enter_time')
    doc_number = request.form.get('doc_number')

    # 获取产品信息
    product_ids = request.form.getlist('product_id')
    numbers = request.form.getlist('number')

    # print(customer_id, enter_time, doc_number, product_ids, numbers)
    files = request.files.getlist('fileInput')

    # 验证表单数据
    if not customer_id:
        return jsonify({'message': '请先选择客户！'}), 400

    if not enter_time:
        return jsonify({'message': '单据日期不能为空！'}), 400

    if not doc_number:
        return jsonify({'message': '单据编号不能为空！'}), 400

    for product_id, number in zip(product_ids, numbers):
        if not product_id:
            return jsonify({'message': '请选择产品！'}), 400

        elif not number:
            return jsonify({'message': '数量不能为空！'}), 400

    conn, cursor = create_db_connection()
    insert_query = '''
    INSERT INTO warehousing_entry_list (customer_id, enter_time, document_number) 
    VALUES (%s, %s, %s)
    '''
    values = (customer_id, enter_time, doc_number)
    cursor.execute(insert_query, values)

    conn.commit()

    cursor.close()
    conn.close()

    for product_id, number in zip(product_ids, numbers):
        conn, cursor = create_db_connection()

        cursor.execute("SELECT purchase_price FROM product_list WHERE id = %s", (product_id,))
        purchase_price = cursor.fetchone()
        purchase_price = purchase_price[0]

        insert_query = '''
        INSERT INTO warehousing_entry_info (document_number, product_id, quantity, purchase_price) 
        VALUES (%s, %s, %s, %s)
        '''
        values = (doc_number, product_id, number, purchase_price)
        cursor.execute(insert_query, values)

        # 写入库存表
        # 查询是否存在指定的 product_id
        select_query = '''
        SELECT * FROM inventory WHERE product_id = %s
        '''
        cursor.execute(select_query, (product_id,))
        result = cursor.fetchone()

        if result:
            # 如果存在，则更新 quantity
            update_query = '''
            UPDATE inventory SET quantity = quantity + %s WHERE product_id = %s
            '''
            cursor.execute(update_query, (number, product_id))
        else:
            # 如果不存在，则插入新行
            insert_query = '''
            INSERT INTO inventory (product_id, quantity) 
            VALUES (%s, %s)
            '''
            values = (product_id, number)
            cursor.execute(insert_query, values)



        conn.commit()

        cursor.close()
        conn.close()

    files = request.files.getlist('fileInput')
    if files and any(file.filename for file in files):
        for file_number, file in enumerate(files, start=1):
            current_time = datetime.now().strftime("%Y%m%d%H%M%S")
            BASE_DIR = os.path.dirname(os.path.abspath(__file__))
            UPLOAD_FOLDER = os.path.join(BASE_DIR, '../static/data/warehousing_entry_file')
            if not os.path.exists(UPLOAD_FOLDER):
                os.makedirs(UPLOAD_FOLDER)

            original_filename = file.filename
            filename_parts = os.path.splitext(original_filename)
            filename_without_extension = filename_parts[0]
            file_extension = filename_parts[1]

            # 创建临时文件来保存上传的文件
            with tempfile.NamedTemporaryFile(delete=False) as temp_file:
                file.save(temp_file.name)
                file_type = imghdr.what(temp_file.name)

                if file_type:
                    # 图片文件，进行压缩保存
                    filename = f"{filename_without_extension}_{current_time}{file_extension}"
                    file_path = os.path.join(UPLOAD_FOLDER, filename)
                    compress_and_save_image(temp_file, file_path)
                else:
                    # 非图片文件，直接保存
                    filename = f"{filename_without_extension}_{current_time}{file_extension}"
                    file_path = os.path.join(UPLOAD_FOLDER, filename)
                    file.save(file_path)

            relative_path = 'data/warehousing_entry_file/' + filename

            conn, cursor = create_db_connection()
            insert_query = '''
            INSERT INTO warehousing_entry_attachment (document_number, filename) 
            VALUES (%s, %s)
            '''
            values = (doc_number, relative_path)
            cursor.execute(insert_query, values)

            conn.commit()
            cursor.close()
            conn.close()

    return jsonify({'message': '表单提交成功！'})
